{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-06-10T03:04:45.167891Z",
     "start_time": "2020-06-10T03:04:44.434849Z"
    }
   },
   "outputs": [],
   "source": [
    "import json\n",
    "from pprint import pprint\n",
    "import pandas as pd\n",
    "from collections import OrderedDict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-06-10T03:04:45.191892Z",
     "start_time": "2020-06-10T03:04:45.171891Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'a': 'b', 'b': 'c', 'colum-d': 'b', 'colum-e': 'c'},\n",
       " {'a': 'b', 'b': 'c', 'colum-name': 'zhangsan'},\n",
       " {'x': 'b', 'y': 'd', 'colum-d': 'b', 'colum-e': 'c'},\n",
       " {'x': 'b', 'y': 'd', 'colum-name': 'zhangsan'}]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def cartesian_product_expand(ds, json_o, colum):\n",
    "    final_ds = []\n",
    "    for d in ds:\n",
    "        for o in json_o:\n",
    "            dd = {}\n",
    "            dd.update(d)\n",
    "            for k, v in o.items():\n",
    "                dd[f\"{colum}-{k}\"] = v\n",
    "            final_ds.append(dd)\n",
    "    return final_ds\n",
    "\n",
    "colum = \"colum\"\n",
    "json_o = [{'d': 'b', 'e': 'c'}, {\"name\": \"zhangsan\"}]\n",
    "ds = [{'a': 'b', 'b': 'c'}, {'x': 'b', 'y': 'd'}]\n",
    "cartesian_product_expand(ds, json_o, colum)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-06-10T03:04:45.211893Z",
     "start_time": "2020-06-10T03:04:45.198892Z"
    }
   },
   "outputs": [],
   "source": [
    "def json_unfold(df):\n",
    "    result = []\n",
    "    for i, row in df.iterrows():\n",
    "        colums = row.index.values\n",
    "        ds = [OrderedDict()]\n",
    "        for colum in colums:\n",
    "            #             print(colum, row[colum], type(row[colum]))\n",
    "            try:\n",
    "                if isinstance(row[colum], list) or isinstance(\n",
    "                        row[colum], dict):\n",
    "                    json_o = row[colum]\n",
    "                else:\n",
    "                    json_o = json.loads(row[colum])\n",
    "                if isinstance(json_o, list) and len(json_o) > 0 and isinstance(\n",
    "                        json_o[0], dict):\n",
    "                    ds = cartesian_product_expand(ds, json_o, colum)\n",
    "                else:\n",
    "                    for k, v in json_o.items():\n",
    "                        for d in ds:\n",
    "                            d[f\"{colum}-{k}\"] = v\n",
    "            except:\n",
    "                for d in ds:\n",
    "                    d[colum] = row[colum]\n",
    "        for d in ds:\n",
    "            result.append(d)\n",
    "    result_df = pd.DataFrame(result)\n",
    "    result_df.dropna(axis=1, how='all', inplace=True)\n",
    "    return result_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-06-10T03:04:45.410905Z",
     "start_time": "2020-06-10T03:04:45.218894Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>序号</th>\n",
       "      <th>标注人员</th>\n",
       "      <th>标注结果</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>王小二</td>\n",
       "      <td>[{\"tags\":[\"123\",\"231\"],\"cc\":{\"tags\":54,\"data\":...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>王小二</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>杨六</td>\n",
       "      <td>{\"tags\":[\"96\",\"f2931\"],\"username\":\"楼满月\",\"infos...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   序号 标注人员                                               标注结果\n",
       "0   1  王小二  [{\"tags\":[\"123\",\"231\"],\"cc\":{\"tags\":54,\"data\":...\n",
       "1   2  王小二                                                NaN\n",
       "2   3   杨六  {\"tags\":[\"96\",\"f2931\"],\"username\":\"楼满月\",\"infos..."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>序号</th>\n",
       "      <th>标注人员</th>\n",
       "      <th>标注结果-tags</th>\n",
       "      <th>标注结果-cc</th>\n",
       "      <th>标注结果-username</th>\n",
       "      <th>标注结果-infos</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>王小二</td>\n",
       "      <td>[123, 231]</td>\n",
       "      <td>{'tags': 54, 'data': 'cc'}</td>\n",
       "      <td>张三</td>\n",
       "      <td>[{'地点': '北京', '爱好': '足球'}, {'地点': '天津', '爱好': ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>王小二</td>\n",
       "      <td>[23, 2931]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>火星</td>\n",
       "      <td>[{'地点': '天津', '爱好': '足球'}]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>王小二</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>杨六</td>\n",
       "      <td>[96, f2931]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>楼满月</td>\n",
       "      <td>[{'地点': '湖北', '爱好': '足球'}]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   序号 标注人员    标注结果-tags                     标注结果-cc 标注结果-username  \\\n",
       "0   1  王小二   [123, 231]  {'tags': 54, 'data': 'cc'}            张三   \n",
       "1   1  王小二   [23, 2931]                         NaN            火星   \n",
       "2   2  王小二          NaN                         NaN           NaN   \n",
       "3   3   杨六  [96, f2931]                         NaN           楼满月   \n",
       "\n",
       "                                          标注结果-infos  \n",
       "0  [{'地点': '北京', '爱好': '足球'}, {'地点': '天津', '爱好': ...  \n",
       "1                         [{'地点': '天津', '爱好': '足球'}]  \n",
       "2                                                NaN  \n",
       "3                         [{'地点': '湖北', '爱好': '足球'}]  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>序号</th>\n",
       "      <th>标注人员</th>\n",
       "      <th>标注结果-tags</th>\n",
       "      <th>标注结果-cc-tags</th>\n",
       "      <th>标注结果-cc-data</th>\n",
       "      <th>标注结果-username</th>\n",
       "      <th>标注结果-infos-地点</th>\n",
       "      <th>标注结果-infos-爱好</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>王小二</td>\n",
       "      <td>[123, 231]</td>\n",
       "      <td>54.0</td>\n",
       "      <td>cc</td>\n",
       "      <td>张三</td>\n",
       "      <td>北京</td>\n",
       "      <td>足球</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>王小二</td>\n",
       "      <td>[123, 231]</td>\n",
       "      <td>54.0</td>\n",
       "      <td>cc</td>\n",
       "      <td>张三</td>\n",
       "      <td>天津</td>\n",
       "      <td>篮球</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>王小二</td>\n",
       "      <td>[23, 2931]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>火星</td>\n",
       "      <td>天津</td>\n",
       "      <td>足球</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2</td>\n",
       "      <td>王小二</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3</td>\n",
       "      <td>杨六</td>\n",
       "      <td>[96, f2931]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>楼满月</td>\n",
       "      <td>湖北</td>\n",
       "      <td>足球</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   序号 标注人员    标注结果-tags  标注结果-cc-tags 标注结果-cc-data 标注结果-username  \\\n",
       "0   1  王小二   [123, 231]          54.0           cc            张三   \n",
       "1   1  王小二   [123, 231]          54.0           cc            张三   \n",
       "2   1  王小二   [23, 2931]           NaN          NaN            火星   \n",
       "3   2  王小二          NaN           NaN          NaN           NaN   \n",
       "4   3   杨六  [96, f2931]           NaN          NaN           楼满月   \n",
       "\n",
       "  标注结果-infos-地点 标注结果-infos-爱好  \n",
       "0            北京            足球  \n",
       "1            天津            篮球  \n",
       "2            天津            足球  \n",
       "3           NaN           NaN  \n",
       "4            湖北            足球  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df = pd.read_excel('data.xlsx')\n",
    "display(df)\n",
    "for i in range(2):\n",
    "    df = json_unfold(df)\n",
    "    display(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-06-10T03:10:24.146279Z",
     "start_time": "2020-06-10T03:10:24.119278Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>class</th>\n",
       "      <th>max_speed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>falcon</td>\n",
       "      <td>bird</td>\n",
       "      <td>389.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>parrot</td>\n",
       "      <td>bird</td>\n",
       "      <td>24.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>lion</td>\n",
       "      <td>mammal</td>\n",
       "      <td>80.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>monkey</td>\n",
       "      <td>mammal</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     name   class  max_speed\n",
       "0  falcon    bird      389.0\n",
       "2  parrot    bird       24.0\n",
       "3    lion  mammal       80.5\n",
       "1  monkey  mammal        NaN"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "data = pd.DataFrame([('falcon', 'bird', 389.0), ('parrot', 'bird', 24.0),\n",
    "                   ('lion', 'mammal', 80.5), ('monkey', 'mammal', np.nan)],\n",
    "                  columns=['name', 'class', 'max_speed'],\n",
    "                  index=[0, 2, 3, 1])\n",
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-06-10T03:28:06.753057Z",
     "start_time": "2020-06-10T03:28:06.663052Z"
    }
   },
   "outputs": [],
   "source": [
    "data.to_excel(\"D:/hdfs/excel/经销商信息.xlsx\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-06-10T03:28:43.153139Z",
     "start_time": "2020-06-10T03:28:42.970128Z"
    }
   },
   "outputs": [],
   "source": [
    "data = pd.read_excel(\"D:/hdfs/excel/经销商信息.xlsx\")\n",
    "\n",
    "for i, df in data.groupby(\"经销商名称\"):\n",
    "    df.to_excel(f\"D:/hdfs/excel/result/{i}.xlsx\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.0"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
